package com.gosophia.metadataEngine.commons;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.util.Assert;

import com.gosophia.commons.exception.BusinessException;
import com.gosophia.metadataEngine.entity.DataSourceInfo;
import com.gosophia.metadataEngine.entity.MetaDataField;

/**
 *数据库工具类
 * @author mayue
 *
 */
public class DBUtil {
    /**
     * 读取表结构：字段名称，长度 生成MetaDataField对象
     * @param dsi
     * @param tableName
     * @return
     */
    public static List<MetaDataField> readTableStructure(DataSourceInfo dsi,String tableName) throws BusinessException{
        Assert.notNull(dsi,"dsi不能为null");
        Assert.notNull(dsi.getDriverClass(),"driverClass不能为null");
        Assert.notNull(dsi.getUrl(),"url不能为null");
        Assert.notNull(tableName,"tableName不能为null");
        List<MetaDataField> list = new ArrayList<MetaDataField>();
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSetMetaData rsmd = null;
        try {
            Class.forName(dsi.getDriverClass());
            connection = DriverManager.getConnection(
                    dsi.getUrl(), dsi.getUsername(),dsi.getPassword());
            ResultSet rs = null;
            DatabaseMetaData meta = connection.getMetaData();
//            rs = meta.getTables(null, "WANGXINBING", tableName.toUpperCase(), null);
//            while(rs.next()){
//            System.out.println(rs.getString("TABLE_NAME"))	;
//            }
            //获取该表主键
            // The Oracle database stores its table names as Upper-Case,
            // if you pass a table name in lowercase characters, it will not work.
            // MySQL database does not care if table name is uppercase/lowercase.      
            rs = meta.getPrimaryKeys(null, null, tableName.toUpperCase());
            
            String sql = "select * from "+tableName.toUpperCase();
            pstmt = connection.prepareStatement(sql);
            pstmt.execute();//oracle要写这行，mysql不用写
            rsmd = pstmt.getMetaData();
            List<String> primaryLists  = new ArrayList<String>();
            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");
                primaryLists.add(columnName);
                //System.out.println("getPrimaryKeys(): columnName=" + columnName);
            }
            for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
                MetaDataField mdf = new MetaDataField();
                mdf.setColName(rsmd.getColumnName(i));
                mdf.setColType(rsmd.getColumnTypeName(i));
                mdf.setColWidth(rsmd.getColumnDisplaySize(i));
                if(primaryLists.contains(rsmd.getColumnName(i))){
                	mdf.setIsPk(true);
                }
                //mdf.setIsPk(isPk)
                list.add(mdf);
            }
        }catch (ClassNotFoundException cnfex) {
            throw new BusinessException("名称为"+tableName+"的表不存在！","JC2_YSJ_B_4");
        }catch (SQLException sqlex) {
            throw new BusinessException("数据源"+dsi.getName()+"无法连接数据库","JC2_YSJ_B_5");
        }
        return list;
    }
    /**
     * 测试数据源是否成功连接数据库
     * @param dsi
     * @return
     */
    public static boolean isConnectioned(DataSourceInfo dsi){
        Assert.notNull(dsi,"数据源参数不能为空");
        Assert.notNull(dsi.getDriverClass(),"driveClass不能为null");
        Assert.notNull(dsi.getUrl(),"url不能为null");
        Connection connection = null;
        try {
            Class.forName(dsi.getDriverClass());
            connection = DriverManager.getConnection(
                         dsi.getUrl(), dsi.getUsername(),dsi.getPassword());
            if(connection != null){
                return true;
            }else{
                return false;
            }
        }catch(ClassNotFoundException e) {
            return false;
        }catch(SQLException e) {
            return false;
        }
        
    }
    
    
//    public static void main(String args[]){
//      //----通过指定数据源读取指定表结构----------
//      DataSourceInfo dsi = new DataSourceInfo();
//      dsi.setDriverClass("oracle.jdbc.driver.OracleDriver");
//      dsi.setUrl("jdbc:oracle:thin:@dev:1521:gzTestDB");
//      dsi.setUsername("tianchunyu");
//      dsi.setPassword("tianchunyu");
//      try {
//          readTableStructure(dsi,"aa");
//      } catch (Exception e) {
//          e.printStackTrace();
//      }
//      //----测试数据源是否可以链接数据库----------
//      DataSourceInfo dsi1 = new DataSourceInfo();
//      dsi1.setDriverClass("aa");
//      dsi1.setUrl("ddd");
//      boolean flag = connectionTest(dsi1);
//      System.out.println(flag);
//  }


}
